using System.Data;
using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;
using static RBAC.ErrorCode.APIEnums;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 销售订单计划查询（图2条件）分页列表
    /// 来源：Delivery_notice 及其关联表
    /// </summary>
    public class GetSalesOrderPlanListByBillHandler : IRequestHandler<GetSalesOrderPlanListByBillCommand, APIResult<GetSalesOrderPlanListByBillDto>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public GetSalesOrderPlanListByBillHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<APIResult<GetSalesOrderPlanListByBillDto>> Handle(GetSalesOrderPlanListByBillCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var connection = _dbConnectionFactory.CreateConnection();

                var whereList = new List<string>();
                var p = new DynamicParameters();

                // 固定未删除条件
                var baseWhere = "dn.IsDeleted = 0";

                if (!string.IsNullOrWhiteSpace(request.SalesOrderCode))
                {
                    whereList.Add("dn.Sales_order_code LIKE @SalesOrderCode");
                    p.Add("@SalesOrderCode", $"%{request.SalesOrderCode.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.BillCode))
                {
                    whereList.Add("dn.Bill_code LIKE @BillCode");
                    p.Add("@BillCode", $"%{request.BillCode.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.CustomerName))
                {
                    whereList.Add("c.CustomerName LIKE @CustomerName");
                    p.Add("@CustomerName", $"%{request.CustomerName.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.SalesmanName))
                {
                    whereList.Add("u.UserName LIKE @SalesmanName");
                    p.Add("@SalesmanName", $"%{request.SalesmanName.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    whereList.Add("dni.Material_name LIKE @MaterialName");
                    p.Add("@MaterialName", $"%{request.MaterialName.Trim()}%");
                }
                if (request.Status.HasValue)
                {
                    whereList.Add("dn.Status = @Status");
                    p.Add("@Status", request.Status.Value);
                }
                if (request.StartDate.HasValue)
                {
                    whereList.Add("dn.Bill_date >= @StartDate");
                    p.Add("@StartDate", request.StartDate.Value.Date);
                }
                if (request.EndDate.HasValue)
                {
                    whereList.Add("dn.Bill_date <= @EndDate");
                    p.Add("@EndDate", request.EndDate.Value.Date.AddDays(1).AddSeconds(-1));
                }

                var whereClause = whereList.Count > 0
                    ? $"WHERE {baseWhere} AND " + string.Join(" AND ", whereList)
                    : $"WHERE {baseWhere}";

                // 统计总数（按明细行）
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM Delivery_notice dn
                    LEFT JOIN Customer c ON dn.Customer_id = c.Id AND c.IsDeleted = 0
                    LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND dni.IsDeleted = 0
                    LEFT JOIN Mater m ON dni.Material_id = m.Id AND m.IsDeleted = 0
                    LEFT JOIN User u ON dn.Salesman_id = u.Id AND u.IsDeleted = 0
                    {whereClause}";

                var total = await connection.ExecuteScalarAsync<int>(countSql, p);

                // 分页数据
                var offset = (request.PageIndex - 1) * request.PageSize;
                var dataSql = $@"
                    SELECT
                        (@row_number:=@row_number + 1) AS SerialNumber,
                        dn.Sales_order_code AS SalesOrderCode,
                        u.UserName AS SalesmanName,
                        dn.Bill_code AS BillCode,
                        CASE
                            WHEN dn.Status = 0 THEN '开立'
                            WHEN dn.Status = 1 THEN '审核'
                            WHEN dn.Status = 2 THEN '退回'
                            ELSE '未知'
                        END AS StatusDescription,
                        c.CustomerName AS CustomerName,
                        dni.Material_name AS MaterialName,
                        dni.Material_code AS MaterialCode,
                        dni.Package_qty AS PieceCount,
                        dni.Qty AS DeliveryQuantity,
                        0 AS OutboundQuantity,
                        dn.Bill_date AS BillDate,
                        dn.Delivery_date AS DeliveryDate
                    FROM Delivery_notice dn
                    LEFT JOIN Customer c ON dn.Customer_id = c.Id AND c.IsDeleted = 0
                    LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND dni.IsDeleted = 0
                    LEFT JOIN Mater m ON dni.Material_id = m.Id AND m.IsDeleted = 0
                    LEFT JOIN User u ON dn.Salesman_id = u.Id AND u.IsDeleted = 0
                    CROSS JOIN (SELECT @row_number := {offset}) r
                    {whereClause}
                    ORDER BY dn.Bill_date DESC, dn.Id DESC
                    LIMIT @PageSize OFFSET @Offset";

                p.Add("@Offset", offset);
                p.Add("@PageSize", request.PageSize);

                var rows = await connection.QueryAsync<SalesOrderPlanByBillItemDto>(dataSql, p);

                return new APIResult<GetSalesOrderPlanListByBillDto>
                {
                    Code = Success,
                    Msg = "查询成功",
                    Data = new GetSalesOrderPlanListByBillDto
                    {
                        TotalCount = total,
                        Items = rows.ToList()
                    }
                };
            }
            catch (Exception ex)
            {
                return new APIResult<GetSalesOrderPlanListByBillDto>
                {
                    Code = Error,
                    Msg = $"查询失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
}
